package demo.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;


import demo.Util.DBUtil;
import demo.Util.GetTimeUtil;
import demo.entity.Order;
import jdk.nashorn.internal.codegen.CompilerConstants.Call;

public class OrderDAO {
	public int save(Order order) {
		Connection con = DBUtil.getConnection();
		String sqlStr = "insert into ordertable (orderdate, price, status) values (?, ?, ?)";
		if(con != null) {
			try {
				PreparedStatement preparedStatement = con.prepareStatement(sqlStr);
				preparedStatement.setTimestamp(1, order.getDate());
				preparedStatement.setDouble(2, order.getPrice());
				preparedStatement.setInt(3, order.getStatus());
				
				return preparedStatement.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return -1;
	}
	
	public int deleteById(int id) {
		Connection con = DBUtil.getConnection();
		String sqlStr = "delete from ordertable where id = ?";
		if(con != null) {
			try {
				if(findById(id) == null) return 0;
				
				PreparedStatement preparedStatement = con.prepareStatement(sqlStr);
				preparedStatement.setInt(1, id);
				
				return preparedStatement.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return -1;
	}
	
	public int update(Order order) {
		Connection con = DBUtil.getConnection();
		String sqlStr = "update ordertable set orderdate = ?, price = ?, status = ? where id = ?";
		if(con != null) {
			try {
				if(findById(order.getId()) == null) return 0;
				
				PreparedStatement preparedStatement = con.prepareStatement(sqlStr);
				preparedStatement.setTimestamp(1, order.getDate());
				preparedStatement.setDouble(2, order.getPrice());
				preparedStatement.setInt(3, order.getStatus());
				preparedStatement.setInt(4, order.getId());
				
				return preparedStatement.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return -1;
	}
	

	public Order findById(int id) {
		Connection con = DBUtil.getConnection();
		String sqlStr = "select * from ordertable where id = " + id;
		if(con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(sqlStr);
				List<Order> listOrder = rsToOrder(rs);
				
				if(listOrder != null && listOrder.size() != 0)
					return listOrder.get(0);
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	public List<Order> findByDate(int type){
		Connection con = DBUtil.getConnection();
		String sqlStr = "select * from ordertable where orderdate >= ? and orderdate <= ? and status = 0";
		Timestamp startTime, endTime;
		if(type == 1) {
			startTime = GetTimeUtil.getStartOfDay(); 
			endTime = GetTimeUtil.getEndOfDay();
		}
		else if(type == 2){
			startTime = GetTimeUtil.getStartOfMonth(); 
			endTime = GetTimeUtil.getEndOfMonth();
		}
		else {
			startTime = GetTimeUtil.getStartOfYear(); 
			endTime = GetTimeUtil.getEndOfYear();
		}
		
		if(con != null) {
			try {
				PreparedStatement preparedStatement = con.prepareStatement(sqlStr);
				preparedStatement.setTimestamp(1, startTime);
				preparedStatement.setTimestamp(2, endTime);
				ResultSet rs = preparedStatement.executeQuery();
				List<Order> listOrder = rsToOrder(rs);
				
				return listOrder;
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	public List<Order> findAll(int status){
		Connection con = DBUtil.getConnection();
		String sqlStr = "select * from ordertable";
		if(status == 1) sqlStr += " where status = 1";
		if(status == 0) sqlStr += " where status = 0";
		if(con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(sqlStr);
				List<Order> listOrder = rsToOrder(rs);
				
				return listOrder;
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		return null;
	}
	
	public int getMaxId() {
		Connection con = DBUtil.getConnection();
		String sqlStr = "select * from ordertable where id=(select MAX(id) from ordertable)";
		if(con != null) {
			try {
				ResultSet rs = con.createStatement().executeQuery(sqlStr);
				List<Order> listOrder = rsToOrder(rs);
				
				if(listOrder != null && listOrder.size() != 0)
					return listOrder.get(0).getId();
			}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeConnection(con);
			}
		}
		
		return 0;
	}
	
	private List<Order> rsToOrder(ResultSet rs){
		List<Order> listOrder = new ArrayList<>();
		try {
			while(rs.next()) {
				int id = rs.getInt(1);
				Timestamp date = rs.getTimestamp(2);
				double price = rs.getDouble(3);
				int status = rs.getInt(4);
				
				Order order = new Order(id, date, price, status);
				listOrder.add(order);
			}
			return listOrder;
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) {
		for(int i = 1; i <= 3; i++) {
			System.out.println((new OrderDAO().findByDate(i)).size());
			System.out.println((new OrderDAO().findAll(1)));
		}
		System.out.println("fin");
	}
}
